﻿namespace AnyOffice.DAL
{
    using AnyOffice.Components;
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class NewsDao
    {
        public bool Delete(SqlConnection conn, int newsId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Attachment] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[AttachmentId] IN ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t[dbo].[News]  ");
            builder.Append("\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\t[NewsId] = @NewsId ");
            builder.Append("\t\t\t\t\tAND ISNULL([AttachmentId],'') <> ''\t\t\t\t\t ");
            builder.Append("\t\t\t) ");
            builder.Append("DELETE FROM [dbo].[News_Comment] ");
            builder.Append("WHERE ");
            builder.Append("\t[NewsId] = @NewsId ");
            builder.Append("DELETE FROM [dbo].[News] ");
            builder.Append("WHERE ");
            builder.Append("\t[NewsId] = @NewsId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@NewsId", SqlDbType.Int) };
            commandParameters[0].Value = newsId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, NewsData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[News] ");
            builder.Append("( ");
            builder.Append("\t[TypeId], ");
            builder.Append("\t[FromId], ");
            builder.Append("\t[ToId], ");
            builder.Append("\t[ToPriv], ");
            builder.Append("\t[ToUser], ");
            builder.Append("\t[Subject], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[SendTime], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[ViewCount], ");
            builder.Append("\t[Readers] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@TypeId, ");
            builder.Append("\t@FromId, ");
            builder.Append("\t@ToId, ");
            builder.Append("\t@ToPriv, ");
            builder.Append("\t@ToUser, ");
            builder.Append("\t@Subject, ");
            builder.Append("\t@Content, ");
            builder.Append("\t@SendTime, ");
            builder.Append("\t@AttachmentId, ");
            builder.Append("\t@ViewCount, ");
            builder.Append("\t@Readers ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[11];
            commandParameters[0] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[0].Value = data.TypeId;
            commandParameters[1] = new SqlParameter("@FromId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.FromId;
            commandParameters[2] = new SqlParameter("@ToId", SqlDbType.NText);
            if (data.ToIdIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.ToId;
            }
            commandParameters[3] = new SqlParameter("@ToPriv", SqlDbType.NText);
            if (data.ToPrivIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.ToPriv;
            }
            commandParameters[4] = new SqlParameter("@ToUser", SqlDbType.NText);
            if (data.ToUserIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.ToUser;
            }
            commandParameters[5] = new SqlParameter("@Subject", SqlDbType.NVarChar, 200);
            commandParameters[5].Value = data.Subject;
            commandParameters[6] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Content;
            }
            commandParameters[7] = new SqlParameter("@SendTime", SqlDbType.DateTime);
            commandParameters[7].Value = data.SendTime;
            commandParameters[8] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.AttachmentId;
            }
            commandParameters[9] = new SqlParameter("@ViewCount", SqlDbType.Int);
            commandParameters[9].Value = data.ViewCount;
            commandParameters[10] = new SqlParameter("@Readers", SqlDbType.NText);
            if (data.ReadersIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.Readers;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByNewsId(SqlConnection conn, int newsId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[News] ");
            builder.Append("SET ");
            builder.Append("\t[ViewCount] = [ViewCount] + 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[NewsId] = @NewsId ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[News] ");
            builder.Append("SET ");
            builder.Append("\t[Readers] = ISNULL(CAST([Readers] AS nvarchar(4000)),'') + @UserId + ',' ");
            builder.Append("WHERE ");
            builder.Append("\t[NewsId] = @NewsId ");
            builder.Append("\tAND NOT CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([Readers] AS nvarchar(4000)),'') + ',') > 0 ");
            builder.Append("SELECT ");
            builder.Append("\t[News].*, ");
            builder.Append("\tdbo.GetDeptNames(CAST([News].[ToId] AS nvarchar(2000))) AS ToDeptNames, ");
            builder.Append("\tdbo.GetPrivNames(CAST([News].[ToPriv] AS nvarchar(2000))) AS ToPrivNames, ");
            builder.Append("\tdbo.GetUserNames(CAST([News].[ToUser] AS nvarchar(4000))) AS ToUserNames, ");
            builder.Append("\t[News_Type].[TypeName], ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[News], ");
            builder.Append("\t[dbo].[News_Type], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[News].[NewsId] = @NewsId ");
            builder.Append("\tAND [News].[TypeId] = [News_Type].[TypeId] ");
            builder.Append("\tAND [News].[FromId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@NewsId", SqlDbType.Int);
            commandParameters[0].Value = newsId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "News";
            return set.Tables[0];
        }

        public DataTable SelectMyTable(SqlConnection conn, int deptId, int privId, string userId)
        {
            string commandText = "SELECT TOP " + new ConfigManager().NewsNum.ToString() + " [News].[NewsId], [News].[Subject], [News].[SendTime], [News].[Readers], [News_Type].[TypeName] FROM [dbo].[News], [dbo].[News_Type] WHERE ( CAST([News].[ToId] AS nvarchar(20)) = 'ALL_DEPT' OR (CHARINDEX(',' + CAST(@DeptId AS nvarchar(20)) + ',',',' + CAST([News].[ToId] AS nvarchar(2000)) + ',') > 0) OR (CHARINDEX(',' + CAST(@PrivId AS nvarchar(20)) + ',',',' + CAST([News].[ToPriv] AS nvarchar(2000)) + ',') > 0) OR (CHARINDEX(',' + @UserId + ',',',' + CAST([News].[ToUser] AS nvarchar(4000)) + ',') > 0) ) AND [News].[TypeId] = [News_Type].[TypeId] ORDER BY [News].[NewsId] DESC";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[0].Value = deptId;
            commandParameters[1] = new SqlParameter("@PrivId", SqlDbType.Int);
            commandParameters[1].Value = privId;
            commandParameters[2] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "News";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount, int typeId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([NewsId]) FROM [dbo].[News] WHERE([TypeId] = @TypeId) OR (@TypeId = 0) ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @indextable table(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t ");
            builder.Append("\t\tINSERT INTO @indextable(PK) SELECT [NewsId] FROM [dbo].[News] WHERE([TypeId] = @TypeId) OR (@TypeId = 0) ORDER BY [NewsId] DESC ");
            builder.Append("\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[News].[NewsId], ");
            builder.Append("\t\t\t[News].[Subject], ");
            builder.Append("\t\t\t[News].[SendTime], ");
            builder.Append("\t\t\tdbo.GetDeptNames(CAST([News].[ToId] AS nvarchar(2000))) AS ToDeptNames, ");
            builder.Append("\t\t\tdbo.GetPrivNames(CAST([News].[ToPriv] AS nvarchar(2000))) AS ToPrivNames, ");
            builder.Append("\t\t\t[News].[ToUser],[News].[ViewCount], ");
            builder.Append("\t\t\t[News_Type].[TypeName], ");
            builder.Append("\t\t\t[Users].[UserName], ");
            builder.Append("\t\t\t[Department].[DeptName], ");
            builder.Append("\t\t\t@pageindex AS pageindex  ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[News], ");
            builder.Append("\t\t\t[dbo].[News_Type], ");
            builder.Append("\t\t\t[dbo].[Users], ");
            builder.Append("\t\t\t[dbo].[Department], ");
            builder.Append("\t\t\t@indextable t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t(([News].[TypeId] = @TypeId) OR (@TypeId = 0)) ");
            builder.Append("\t\t\tAND [News].[TypeId] = [News_Type].[TypeId] ");
            builder.Append("\t\t\tAND [News].[FromId] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\t\t\tAND [News].[NewsId] = t.PK ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[News].[NewsId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[3].Value = typeId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "News";
            return set.Tables[0];
        }
        /// <summary>
        /// 个人简报查询
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="pagesize"></param>
        /// <param name="pageindex"></param>
        /// <param name="docount"></param>
        /// <param name="deptId"></param>
        /// <param name="privId"></param>
        /// <param name="userId"></param>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public DataTable SelectShowPaged(SqlConnection conn, int pagesize, int pageindex, bool docount, int deptId, int privId, string userId, int typeId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("BEGIN ");
            builder.Append("\tSELECT COUNT([NewsId]) FROM [dbo].[News] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t( ");
            builder.Append("\t\t\tCAST([ToId] AS nvarchar(20)) = 'ALL_DEPT'  ");
            builder.Append("\t\t\tOR (CHARINDEX(',' + CAST(@DeptId AS nvarchar(20)) + ',',',' + CAST([ToId] AS nvarchar(2000)) + ',') > 0) ");
            builder.Append("\t\t\tOR (CHARINDEX(',' + CAST(@PrivId AS nvarchar(20)) + ',',',' + CAST([ToPriv] AS nvarchar(2000)) + ',') > 0) ");
            builder.Append("\t\t\tOR (CHARINDEX(',' + @UserId + ',',',' + CAST([FromId] AS nvarchar(4000)) + ',') > 0) ");
            builder.Append("\t\t) ");
            builder.Append("\t\tAND ([TypeId] = @TypeId OR @TypeId = 0) ");
            builder.Append("END ");
            builder.Append("ELSE ");
            builder.Append("BEGIN ");
            builder.Append("\tDECLARE @indextable table(ID int identity(1,1),PK int) ");
            builder.Append("\tDECLARE @PageLowerBound int ");
            builder.Append("\tDECLARE @PageUpperBound int ");
            builder.Append("\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\tSET rowcount @PageUpperBound ");
            builder.Append("\tINSERT INTO @indextable(PK) SELECT [NewsId] FROM [dbo].[News] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t( ");
            builder.Append("\t\t\tCAST([ToId] AS nvarchar(20)) = 'ALL_DEPT'  ");
            builder.Append("\t\t\tOR (CHARINDEX(',' + CAST(@DeptId AS nvarchar(20)) + ',',',' + CAST([ToId] AS nvarchar(2000)) + ',') > 0) ");
            builder.Append("\t\t\tOR (CHARINDEX(',' + CAST(@PrivId AS nvarchar(20)) + ',',',' + CAST([ToPriv] AS nvarchar(2000)) + ',') > 0) ");
            builder.Append("\t\t\tOR (CHARINDEX(',' + @UserId + ',',',' + CAST([FromId] AS nvarchar(4000)) + ',') > 0) ");
            builder.Append("\t\t) ");
            builder.Append("\t\tAND ([TypeId] = @TypeId OR @TypeId = 0) ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[NewsId] DESC ");
            builder.Append("\t ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[News].[NewsId], ");
            builder.Append("\t\t[News].[Subject], ");
            builder.Append("\t\tdbo.GetDeptNames(CAST([News].[ToId] AS nvarchar(2000))) AS ToDeptNames, ");
            builder.Append("\t\tdbo.GetPrivNames(CAST([News].[ToPriv] AS nvarchar(2000))) AS ToPrivNames, ");
            builder.Append("\t\tdbo.GetUserNames(CAST([News].[FromId] AS nvarchar(4000))) AS ToUserNames, ");
            builder.Append("\t\t[News].[SendTime], ");
            builder.Append("\t\t[News].[ViewCount], ");
            builder.Append("\t\t[News].[Readers], ");
            builder.Append("\t\t[News_Type].[TypeName], ");
            builder.Append("\t\t[Users].[UserName], [News].[ToUser],");
            builder.Append("\t\t[Department].[DeptName], ");
            builder.Append("\t\t@pageindex AS pageindex ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[News], ");
            builder.Append("\t\t[dbo].[News_Type], ");
            builder.Append("\t\t[dbo].[Users], ");
            builder.Append("\t\t[dbo].[Department], ");
            builder.Append("\t\t@indextable t ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[News].[TypeId] = [News_Type].[TypeId]\t\t ");
            builder.Append("\t\tAND [News].[FromId] = [Users].[UserId] ");
            builder.Append("\t\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\t\tAND [News].[NewsId] = t.PK ");
            builder.Append("\t\tAND t.ID>@PageLowerBound ");
            builder.Append("\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[News].[NewsId] DESC ");
            builder.Append("END ");
            SqlParameter[] commandParameters = new SqlParameter[7];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[3].Value = deptId;
            commandParameters[4] = new SqlParameter("@PrivId", SqlDbType.Int);
            commandParameters[4].Value = privId;
            commandParameters[5] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[5].Value = userId;
            commandParameters[6] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[6].Value = typeId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "News";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, NewsData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[News] ");
            builder.Append("SET ");
            builder.Append("\t[TypeId] = @TypeId, ");
            builder.Append("\t[FromId] = @FromId, ");
            builder.Append("\t[ToId] = @ToId, ");
            builder.Append("\t[ToPriv] = @ToPriv, ");
            builder.Append("\t[ToUser] = @ToUser, ");
            builder.Append("\t[Subject] = @Subject, ");
            builder.Append("\t[Content] = @Content, ");
            builder.Append("\t[SendTime] = @SendTime, ");
            builder.Append("\t[AttachmentId] = @AttachmentId ");
            builder.Append("WHERE ");
            builder.Append("\t[NewsId] = @NewsId ");
            SqlParameter[] commandParameters = new SqlParameter[10];
            commandParameters[0] = new SqlParameter("@NewsId", SqlDbType.Int);
            commandParameters[0].Value = data.NewsId;
            commandParameters[1] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[1].Value = data.TypeId;
            commandParameters[2] = new SqlParameter("@FromId", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.FromId;
            commandParameters[3] = new SqlParameter("@ToId", SqlDbType.NText);
            if (data.ToIdIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.ToId;
            }
            commandParameters[4] = new SqlParameter("@ToPriv", SqlDbType.NText);
            if (data.ToPrivIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.ToPriv;
            }
            commandParameters[5] = new SqlParameter("@ToUser", SqlDbType.NText);
            if (data.ToUserIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.ToUser;
            }
            commandParameters[6] = new SqlParameter("@Subject", SqlDbType.NVarChar, 200);
            commandParameters[6].Value = data.Subject;
            commandParameters[7] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Content;
            }
            commandParameters[8] = new SqlParameter("@SendTime", SqlDbType.DateTime);
            commandParameters[8].Value = data.SendTime;
            commandParameters[9] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.AttachmentId;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

